﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;
using System.Data.SqlClient;

namespace IHISCETeim.DAL
{
    public  class UClaimDemageControlDAL
    {
        /// <summary>
        /// 添加索赔主记录_服务部填写
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int Add(UClaimDemageControlInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_Add", obj.ClaimsNumber, obj.SerialNumber, obj.FaultDate, obj.FaultDescription, obj.AgentName, obj.CustomerName, obj.MachineType, obj.WorkHour, obj.ProcessMode, obj.AgentPartCost, obj.AgentWorkCost, obj.AgentOutCost, obj.AgentTravelCost, obj.AgentTransportCost, obj.AgentOtherCost, obj.SendPartTransportCost, obj.DutyPanDing, obj.AgentIsSubmit,obj.PiFuDate,obj.PaiGongCiShu);
            return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
        }
        /// <summary>
        /// 删除,将删主从表所有记录
        /// </summary>
        /// <param name="ClaimsNumber"></param>
        /// <returns></returns>
        public static bool Delete(string ClaimsNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_Delete", ClaimsNumber);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 根据索赔单号获取主表记录
        /// </summary>
        /// <param name="ClaimsNumber"></param>
        /// <returns></returns>
        public static IDataReader GetByClaimsNumber(string ClaimsNumber)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_GetByClaimsNumber", ClaimsNumber);
            return DB.ExecuteReader(dbCommand);

	    }
        /// <summary>
        /// 根据索赔单号获取从表所有记录
        /// </summary>
        /// <param name="ClaimsNumber"></param>
        /// <returns></returns>
        public static IDataReader GetDetailByClaimsNumber2(string ClaimsNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_GetDetailByClaimsNumber", ClaimsNumber);
            return DB.ExecuteReader(dbCommand);

        }

        public static DataTable GetDetailByClaimsNumber(string ClaimsNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_GetDetailByClaimsNumber", ClaimsNumber);
            return DB.ExecuteDataSet(dbCommand).Tables[0];

        }
        /// <summary>
        /// 分页 
        /// </summary>
        /// <param name="SQLWhere"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Sort"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public static DataTableCollection GetPagination(string SQLWhere, int PageIndex, int PageSize, string Sort, ref int TotalCount)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_GetPagination");
            DB.AddInParameter(dbCommand, "SQLWhere", DbType.String,SQLWhere);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32,PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32,PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String,Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32,8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;
				
            TotalCount=Convert.ToInt32( DB.GetParameterValue(dbCommand,"TotalCount"));
            return dt;
	    }

        /// <summary>
        /// 分页 
        /// </summary>
        /// <param name="SQLWhere"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Sort"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public static DataTableCollection GetSupplerDetailPagination(string SQLWhere, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_GetSupplerDetailPagination");
            DB.AddInParameter(dbCommand, "SQLWhere", DbType.String, SQLWhere);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }
        /// <summary>
        /// 更新主表记录,根据ClaimsNumber
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool Update(UClaimDemageControlInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_Update", obj.ClaimsNumber, obj.SerialNumber, obj.FaultDate, obj.FaultDescription, obj.AgentName, obj.CustomerName, obj.MachineType, obj.WorkHour, obj.ProcessMode, obj.AgentPartCost, obj.AgentWorkCost, obj.AgentOutCost, obj.AgentTravelCost, obj.AgentTransportCost, obj.AgentOtherCost, obj.FinalDiffAnalyse, obj.SupplerDiffAnalyse, obj.DiffDuty, obj.SendPartTransportCost, obj.DutyPanDing, obj.AgentIsSubmit, obj.PiFuDate,obj.BaoXiaoDate,obj.PingZhengHao,obj.CanSuoPei,obj.PaiGongCiShu,obj.DutyPanDingRemark);
            return Convert.ToInt32(DB.ExecuteScalar(dbCommand)) > 0;

        }
        /// <summary>
        /// 更新结算情况,单条更新
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool UpdateFinalDetail(UClaimDemageControlDetailInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            //DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_UpdateFinalDetail", obj.ControlDetailID, obj.ClaimsNumber, obj.FinalInvoiceDate == DateTime.MinValue ? DBNull.Value : obj.FinalInvoiceDate, obj.FinalInvoiNo, obj.FinalInvoiUS, obj.FinalInvoiUSRate, obj.FinalInvoiJPY, obj.FinalInvoiJPYRate, obj.FinalInvoiRMB, obj.FinalDueDate, obj.FinalPartCost);
            
            //return Convert.ToInt32(DB.ExecuteScalar(dbCommand)) > 0;

            //Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_UpdateFinalDetail");
            DB.AddInParameter(dbCommand, "ControlDetailID", DbType.Int32, obj.ControlDetailID);
            DB.AddInParameter(dbCommand, "ClaimsNumber", DbType.String, obj.ClaimsNumber);//
            DB.AddInParameter(dbCommand, "SuoPeiNum", DbType.String, obj.SuoPeiNum);
           // DB.AddInParameter(dbCommand, "FinalInvoiceDate", DbType.DateTime, obj.FinalInvoiceDate == DateTime.MinValue ? DBNull.Value : obj.FinalInvoiceDate);
            SqlParameter FinalInvoiceDate = new SqlParameter("@FinalInvoiceDate", SqlDbType.DateTime);
            FinalInvoiceDate.IsNullable = true;
            if (obj.FinalInvoiceDate == DateTime.MinValue)
                FinalInvoiceDate.Value = DBNull.Value;
            else
                FinalInvoiceDate.Value = obj.FinalInvoiceDate;

            dbCommand.Parameters.Add(FinalInvoiceDate);

            DB.AddInParameter(dbCommand, "FinalInvoiNo", DbType.String, obj.FinalInvoiNo);
            DB.AddInParameter(dbCommand, "FinalInvoiUS", DbType.Decimal, obj.FinalInvoiUS);
            DB.AddInParameter(dbCommand, "FinalInvoiUSRate", DbType.Decimal, obj.FinalInvoiUSRate);
            DB.AddInParameter(dbCommand, "FinalInvoiJPY", DbType.Decimal, obj.FinalInvoiJPY);
            DB.AddInParameter(dbCommand, "FinalInvoiJPYRate", DbType.Decimal, obj.FinalInvoiJPYRate);
            DB.AddInParameter(dbCommand, "FinalInvoiRMB", DbType.Decimal, obj.FinalInvoiRMB);
            DB.AddInParameter(dbCommand, "FinalDueDate", DbType.Boolean, obj.FinalDueDate);

            //SqlParameter FinalDueDate = new SqlParameter("@FinalDueDate", SqlDbType.DateTime);
            //if (obj.FinalDueDate == DateTime.MinValue)
            //    FinalDueDate.Value = System.DBNull.Value;
            //else
            //    FinalDueDate.Value = obj.FinalDueDate;

            //dbCommand.Parameters.Add(FinalDueDate);

            DB.AddInParameter(dbCommand, "FinalPartCost", DbType.Decimal, obj.FinalPartCost);
            DB.AddInParameter(dbCommand, "FinalPartFanXinCost", DbType.Decimal, obj.FinalPartFanXinCost);
            return Convert.ToInt32(DB.ExecuteScalar(dbCommand)) > 0;
        }
        /// <summary>
        /// 更新向供应商索赔情况,如果ControlDetailID=0,则表示要新增
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool UpdateSupplerDetail(UClaimDemageControlDetailInfo obj)
        {
            
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_UpdateSupplerDetail",obj.ControlDetailID,obj.ClaimsNumber,obj.SuoPeiNum,obj.Duty,obj.OnlyWork,obj.SupplerName,obj.SupplerUS,obj.SupplerUSRate,obj.SupplerJPY,obj.SupplerJPYRate,obj.SupplerRMB,obj.SupplerPartCost,obj.SupplerSubmitDate);

            return Convert.ToInt32(DB.ExecuteScalar(dbCommand)) > 0;
        }
        /// <summary>
        /// 导出明细
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataTableCollection ExportDetailExecl(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_ExportDetailExecl", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 获取索赔统计报表
        /// </summary>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTableCollection GetSupplerTongJiReport(string fromdate, string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_GetSupplerTongJiReport", fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 获取所有品情单号
        /// </summary>
        /// <returns></returns>
        public static DataTable GetALLClaimsNumber()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetSqlStringCommand("SELECT ClaimsNumber,SerialNumber FROM dbo.UClaimDemageControl");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取所有索赔单号
        /// </summary>
        /// <returns></returns>
        public static DataTable GetALLSuoPeiNum()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetSqlStringCommand("SELECT ClaimsNumber,SuoPeiNum FROM dbo.UClaimDemageControlDetail");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }


        /// <summary>
        /// 供应商市厌件统计表
        /// </summary>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTableCollection SupplierShiYanTongJi(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_SupplierShiYanTongJi", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 获取二次派工比率报表
        /// </summary>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <returns></returns>
        public static DataTable GetPaiGongCiShuBiLi(string fromdate, string todate)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_GetPaiGongCiShuBiLi", fromdate, todate);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
    }
}
